One method of organizing a computer database is to separate the data into tables which consist of rows and columns of data. For example, in a table of employee data a row might contain information about a particular employee while a column might contain the department number of each employee. A database will typically have many tables and each table will typically have multiple rows and multiple columns.
Data may be retrieved from this type of database by a variety of methods. For example, a computer program can extract information from the database without human intervention or a user can interact with a query system program which serves as a front-end to the database system. It is conventional for these query system programs to prompt the user for information to make the task of entering the required information for the retrieval easier. These systems are known as prompted query systems (PQS).
A user interacting with a PQS will typically be prompted to specify which tables are to be searched and to specify relationships between various columns of the tables. Specifying the column relationships between tables is called joining the tables. Tables are joined by pairwise association of columns between the tables. Joins allow additional information to be obtained across tables in a meaningful way so that data in one table may explain or clarify data in another table. For example, an Employee-table row for an employee may list the employee's department number as `76`, but the definition of department `76` requires reference to another table, the Department-table, which lists the full department title associated with each department number. In this second table the row for department `76` also contains a column with the department title "Information Systems Department." Thus, a user desiring to generate a report containing a list of all employees including their department titles may want to establish a join relationship between the department number column in the Employee-table and the department title column in the Department-table, so that the employee's department can be printed in the title form instead of the numerical form.
Joins are specified to the PQS by entering statements such as
W and X PA0 X and Y PA0 Y and Z
where W, X, Y and Z are symbolic names given to columns in specific tables. A join implies four pieces of information: two table names and two column names. When the user enters a column name, a table name is implied or referenced indirectly. The table names corresponding to columns W, X, etc., can be written as T(W), T(X), etc. to indicate that the table name can be found based on the column name. The PQS must maintain a join list which contains sufficient information to fully specify all of the current joins. The information in the join list in this context can be symbolic names such as `Q.TAB1`, memory addresses which allow the referenced information to be found, or any other means of indirect referencing. The join list can also be simply the list of join statements.
During an interactive session with a PQS a user may develop the final query through a trial and error process during which the user may make mistakes in specifying tables or joins or may delete a previously specified table or join. For these reasons among others, it is necessary that the join list be analyzed for coherence. When one or more joins are removed from a list that was previously coherent, the list may no longer be coherent. For example, if the X and Y join were deleted from the three joins listed above and T(W), T(X), T(Y), and T(Z) were all separate tables, there would be no link between the W-X pair and the Y-Z pair. When this situation arises a PQS must take some action. In the prior art the action taken is to erase the entire join list and force the user to build a new list of joins. This can cause the user to have to redo a significant amount of work, especially when joins exist between multiple columns of two tables. Thus what is needed is a method and system whereby the PQS analyzes the remaining joins and make an intelligent decision as to which joins can be retained. A method which allows this decision to be made is needed.